<?php

class Model_Classof extends Model_Pandrade {

    const TABLE = "classof";

    /**
     * @var protected $id id value
     * @method int get_id() get_id(void)
     * @method void set_id() set_id(int $id)
     */
    protected $id;

    /**
     * @var protected $institution institution value
     * @method int get_institution() get_institution(void)
     * @method void set_institution() set_institution(int $institution)
     */
    protected $institution;

    /**
     * @var protected $name name value
     * @method string get_name() get_name(void)
     * @method void set_name() set_name(string $name)
     */
    protected $name;

    /**
     * @var protected $start_date start_date value
     * @method date get_start_date() get_start_date(void)
     * @method void set_start_date() set_start_date(date $start_date)
     */
    protected $start_date;

    /**
     * @var protected $end_date end_date value
     * @method date get_end_date() get_end_date(void)
     * @method void set_end_date() set_end_date(date $end_date)
     */
    protected $end_date;

    /**
     * @var protected $type type value
     * @method string get_type() get_type(void)
     * @method void set_type() set_type(string $type)
     */
    protected $type;

    /**
     * @return Model_Classof
     */
    public function __construct($id = 0) {
        if (0 < intval($id)) {
            $db = DB::select("*")->from(self::TABLE)->where("id", "=", $id)->limit(1);
            $db = $db->as_assoc()->execute();
            if ($db->count() > 0) {
                $this->load($db->current());
            }
        }
    }

    protected static function get_database_fields() {
        return array(
            'institution' => __("Classof_Institution"),
            'name' => __("Classof_Name"),
            'start_date' => __("Classof_Start_date"),
            'end_date' => __("Classof_End_date"),
            'type' => __("Classof_Type"),
        );
    }

    public function validate() {
        $validateArray = array();
        foreach (self::get_database_fields() as $key => $value) {
            $validateArray[$key] = $this->get($key);
        }
        $validation = Validation::factory($validateArray)
                ->rule('institution', 'not_empty')
                ->rule('name', 'not_empty')
        ;
        if ($validation->check()) {
            return $validateArray;
        } else {
            throw new Validation_Exception('validation', $validation);
        }
    }

    public static function get_report() {
        return DB::select(
                                "id"
                                , "institution"
                                , "name"
                                , "start_date"
                                , "end_date"
                                , "type"
                                , array("id", "actions"))
                        ->from(self::TABLE)
                        ->order_by("id", "ASC");
    }

    public static function search_classof($classof_expr = NULL, $institution_id = NULL, $institution_city_id = NULL, $institution_start = NULL, $institution_end = NULL) {

        if ($classof_expr === NULL || $institution_id === NULL || $institution_city_id === NULL || $institution_end === NULL || $institution_start === NULL) {
            return array();
        }

        $classofs = DB::select(self::TABLE . ".id", self::TABLE . ".name")->from(self::TABLE)
                ->join(Model_Institution::TABLE)
                ->on(self::TABLE . ".institution", "=", Model_Institution::TABLE . ".id");

        $where_has_been_opened = false;
        
        if ($institution_id != "") {
            $classofs->where(self::TABLE . ".institution", "=", $institution_id);
            $where_has_been_opened = true;
        }
        
        //only use institution_city_id if the institution was not especified
        if($institution_id == "" && $institution_city_id!="" && is_int($institution_city_id)){
            $institutions = Model_Institution::get_institution_from($institution_city_id);
            if(sizeof($institutions)>0){
                if ($where_has_been_opened){
                    $classofs->and_where(self::TABLE.".institution", "IN", $institution_city_id_institutions);
                }else{
                    $classofs->where(self::TABLE.".institution", "IN", $institution_city_id_institutions);
                }
            }
        }
        
        if ($classof_expr != "") {
            //expressions with more than word be like: "%WORD1%WORD2%" (for mysql)
            $words = explode(" ", $classof_expr);
            $size = sizeof($words);
            $i = $size;
            $mysql_pattern = "";
            do {
                $mysql_pattern .= $words[$size - $i];
                $mysql_pattern .= "%";
                $i--;
            } while ($i > 0);
            if ($where_has_been_opened) {
                $classofs->and_where_open();
            } else {
                $classofs->where_open();
            }
            $classofs->where(self::TABLE . ".name", "LIKE", $mysql_pattern)
                    ->or_where(self::TABLE . ".type", "LIKE", $mysql_pattern);
            
            if ($where_has_been_opened) {
                $classofs->and_where_close();
            } else {
                $classofs->where_close();
                $where_has_been_opened = true;
            }
            
        }

        if (false !== Model_Validate::date_mmyy_pair($institution_start, $institution_end)) {
            $from = date("Y-m-d", Model_Validate::extract_timestamp_from_mmyy($institution_start) - 3600 * 24 * 365); //less an year
            $to = date("Y-m-d", Model_Validate::extract_timestamp_from_mmyy($institution_start) + 3600 * 24 * 365); //plus an year
            //->where('orders.date_paid', 'BETWEEN', array($from, $to))
            if ($where_has_been_opened) {
                $classofs->and_where_open();
            } else {
                $classofs->where_open();
            }

            $classofs->or_where_open();
            $classofs->where(self::TABLE . ".start_date", ">=", $from)
                    ->and_where(self::TABLE . ".end_date", "<=", $to);
            $classofs->or_where_close();

            $classofs->or_where_open();
            $classofs->where(self::TABLE . ".start_date", "IS", DB::expr("NULL"))
                    ->and_where(self::TABLE . ".end_date", "<=", $to);
            $classofs->or_where_close();

            $classofs->or_where_open();
            $classofs->where(self::TABLE . ".start_date", ">=", $from)
                    ->and_where(self::TABLE . ".end_date", "IS", DB::expr("NULL"));
            $classofs->or_where_close();

            $classofs->or_where_open();
            $classofs->where(self::TABLE . ".start_date", "IS", DB::expr("NULL"))
                    ->and_where(self::TABLE . ".end_date", "IS", DB::expr("NULL"));
            $classofs->or_where_close();

            if ($where_has_been_opened) {
                $classofs->and_where_close();
            } else {
                $classofs->where_close();
                $where_has_been_opened = true;
            }
        }

        $classofs->limit(15)->order_by(self::TABLE . ".name", "ASC");
//        die($classofs);
        return $classofs->execute()->as_array();
    }

}